package com.jxsd.edu.cn.struts2.daoImpl;

import java.util.List;

import com.jxsd.edu.cn.struts2.dao.PubMsgDao;
import com.jxsd.edu.cn.struts2.models.PubMsgview;
import com.jxsd.edu.cn.tools.Page;
import com.jxsd.edu.cn.tools.PageBean;
import com.jxsd.edu.cn.tools.ResultBean;
import com.jxsd.edu.cn.tools.ResultList;
import com.jxsd.edu.cn.tools.StringTool;
import com.jxsd.edu.cn.tools.Template;

public class PubMsgDaoImpl implements PubMsgDao {

	@Override
	public void doAddPubMsg(PubMsgview  view) {
		Template  t=new  Template();
		String  sql="insert  into  publishmessage(title,author,postTime,describes,smallID,cover)" +
				    "  values(?,?,?,?,?,?) ";
		Object[]  params={view.getTitle(),view.getAuthor(),view.getPostTime(),view.getDescribes(),view.getSmallId(),view.getCover()};
		t.update(sql, params);
	}

	@Override
	public PubMsgview getPubMsg(String publishID) {
		ResultBean<PubMsgview>  hander=new  ResultBean<PubMsgview>();
		String  sql="select  catagoryName,smallName,title,describes,author,nickname,publishID,postTime" +
				    ", visistNumber,smallId,catagoryId,cover,cityName,univerName,image,telephone" +
				    "   from  pubMsgview  where  publishID=?";
		Object[] params={publishID};
		PubMsgview  view=null;
		
		System.out.println("************PubMsgDaoImpl*************");
		System.out.println("publishID:"+publishID);
		System.out.println("************PubMsgDaoImpl*************");
		
		try {
			view=hander.handler(sql, params, PubMsgview.class);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return view;
	}

	@Override
	public Page<PubMsgview> getPagePubMsgTitle(String attriName, String value,PageBean<PubMsgview>  pagebean)
	{
		ResultList<PubMsgview>  hander=new  ResultList<PubMsgview>();
		Object[]  params={value};
		String  sql="select  title,publishID,postTime" +
			    " , cover,smallId  from  pubMsgview  where "+attriName+"=?";
		Page<PubMsgview>  page=null;
		try {
			page=hander.pageHandler(pagebean, sql, params, PubMsgview.class);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return page;
	}

	@Override
	public Page<PubMsgview> getNewlyPubMsg(String catagoryId,PageBean<PubMsgview>  pagebean,String cityName) 
	{
		if(null==cityName)
		{
			cityName="南昌";
		}
	    String  sql="select  catagoryName,publishID,title,cover  from  pubMsgview  where  CatagoryID=? and  cityName=?" +
	    		    "   order  by  postTime  DESC";
	    Object[]  params={catagoryId,cityName};
	    ResultList<PubMsgview>  hander=new  ResultList<PubMsgview>();
	    Page<PubMsgview>  page=null;
	    try {
			page=hander.pageHandler(pagebean, sql, params, PubMsgview.class);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return page;
	}

	@Override
	public List<PubMsgview> getPubMsgs(String smallId, String cityName) 
	{
		String  sql="select  publishID,title  from  pubmsgview  where  smallId=?  and  cityName=?   order  by postTime DESC  limit 0,10";
		Object[]  params={smallId,cityName};
		ResultList<PubMsgview>  hander=new  ResultList<PubMsgview>();
		List<PubMsgview>  list=null;
		try {
			list=hander.handler(sql, params, PubMsgview.class);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public List<PubMsgview> getLabelPubMsgs(String[] smallNames, String cityName,
			String univerName) {
		//拼接SQL语句
		String  sql="(select  rs.publishID,rs.title,rs.cityName,rs.univerName,rs.PostTime,rs.cover  from  "+
                     "( select  p.publishID,p.title,p.cityName,p.univerName,p.PostTime,p.cover  from  pubmsgview as  p  where  ";
		String  sql1="  and   cityName='"+cityName+"')  as  rs"+
                     "  where  univerName='"+univerName+"'  order  by  p.PostTime)"+
                     "  union"+"( select   p1.publishID, p1.title,p1.cityName,p1.univerName,p1.PostTime,p1.cover  from  pubmsgview  as  p1  where   ";
        String  sql2="  and   cityName='"+cityName+"'  and  univerName<>'"+univerName+"'  order  by p1.PostTime)";
		
		String  str="SmallName in( ";
		if(smallNames!=null&&smallNames.length>=0)
		{
		   for(int i=0;i<smallNames.length;i++)
		   {
			   str+="'"+smallNames[i]+"'";
			   if(i!=smallNames.length-1)
			   {
				   str+=",";
			   }
		   }
		   str+=")";
		}
		
		sql+=str;
		sql+=sql1;
		sql+=str;
		sql+=sql2;
		
		System.out.println("SQL:"+sql);
		
		ResultList<PubMsgview>  hander=new  ResultList<PubMsgview>();
		List<PubMsgview>  list=null;
	    try {
	    	list=hander.handlerState(sql, PubMsgview.class);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public Page<PubMsgview> recommendPubMsg(String nickName)
	{
		PageBean<PubMsgview>  pagebean=new  PageBean<PubMsgview>();
		
	    String  sql="  select  publishId,title,p.cityName  from  pubmsgview  as  p ,labelview  as  l,userInfo  as  u"+  
                    "  where  p.nickname =?  and  p.nickName=l.nickname  and  p.SmallName<>l.smallName" +
                    "  and  p.nickname = u.nickname  and  p.cityName = u.cityName"+
                    "  order  by  p.PostTime";
	    
	    Object[]  params={nickName};
	    ResultList<PubMsgview>  hander=new  ResultList<PubMsgview>();
	    Page<PubMsgview>  page=null;
	    
	    try {
			page=hander.pageHandler(pagebean, sql, params, PubMsgview.class);
		} catch (Exception e) {
			e.printStackTrace();
		}
	    
		return page;
	}

	@Override
	public void pubMsg(PubMsgview msg) 
	{
		String  sql="insert  into publishmessage(publishID,Title,Author,PostTime,describes,smallID,cover,telephone,outTime)  values(?,?,?,?,?,?,?,?,'false') ";
		Object[]  params={msg.getPublishID(),msg.getTitle(),msg.getAuthor(),
				                    StringTool.getCurDate(),msg.getDescribes(),msg.getSmallId(),
				                    msg.getCover(),msg.getTelephone()};
		Template  t=new  Template();
		t.update(sql, params);
	}

	@Override
	public void deletePubMsg(String publishId)
	{
		String  sql="delete  from  publishmessage  where  publishId=?";
		Object[]  params={publishId};
		Template  t=new  Template();
		t.update(sql, params);
	}

	@Override
	public void updatePubMsg(PubMsgview msg) 
	{
		String  sql="update  publishmessage  set  Title=?,PostTime=?,describes=?,cover=?,outTime='false'  where  publishId=?";
		Object[]  params={msg.getTitle(),StringTool.getCurDate(),msg.getDescribes(),msg.getCover(),msg.getPublishID()};
		Template  t=new  Template();
		t.update(sql, params);
	}

	@Override
	public Page<PubMsgview> getPagePubMsgTitle(String cityName,
			String attriName, String value, PageBean<PubMsgview> pagebean) {
		ResultList<PubMsgview>  hander=new  ResultList<PubMsgview>();
		Object[]  params={cityName,value};
		String  sql="select  title,publishID,postTime" +
			    " , cover,smallId,smallName,cityName,univerName  from  pubMsgview  where   cityName=?  and "+attriName+"=?";
		Page<PubMsgview>  page=null;
		try {
			page=hander.pageHandler(pagebean, sql, params, PubMsgview.class);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return page;
	}

	@Override
	public List<PubMsgview> queryNewlyMsgs(String cityName) {
		List<PubMsgview>  list=null;
		String  sql="select  publishId,title  from  pubmsgview  where   cityName=?   order  by  PostTime  limit 0,10";
		Object[]  params={cityName};
		ResultList<PubMsgview>  hander=new  ResultList<PubMsgview>();
		try {
			list=hander.handler(sql, params, PubMsgview.class);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}
}
